import { AppDataSource, CrawledData, CrawlerTask } from '@weubi/shared';
import { Repository } from 'typeorm';

export class DataController {
  private dataRepository: Repository<CrawledData>;
  private taskRepository: Repository<CrawlerTask>;

  constructor() {
    this.dataRepository = AppDataSource.getRepository(CrawledData);
    this.taskRepository = AppDataSource.getRepository(CrawlerTask);
  }

  // 获取数据列表
  async getData(query: any) {
    const {
      page = 1,
      limit = 20,
      taskId,
      url,
      engine,
      isValid,
      isProcessed,
      startDate,
      endDate,
      search,
    } = query;

    const queryBuilder = this.dataRepository
      .createQueryBuilder('data')
      .leftJoinAndSelect('data.task', 'task');

    // 添加筛选条件
    if (taskId) {
      queryBuilder.andWhere('data.taskId = :taskId', { taskId });
    }

    if (url) {
      queryBuilder.andWhere('data.url ILIKE :url', { url: `%${url}%` });
    }

    if (engine) {
      queryBuilder.andWhere('data.engine = :engine', { engine });
    }

    if (isValid !== undefined) {
      queryBuilder.andWhere('data.isValid = :isValid', { isValid });
    }

    if (isProcessed !== undefined) {
      queryBuilder.andWhere('data.isProcessed = :isProcessed', { isProcessed });
    }

    if (startDate) {
      queryBuilder.andWhere('data.createdAt >= :startDate', { startDate });
    }

    if (endDate) {
      queryBuilder.andWhere('data.createdAt <= :endDate', { endDate });
    }

    if (search) {
      queryBuilder.andWhere(
        '(data.url ILIKE :search OR CAST(data.data AS TEXT) ILIKE :search)',
        { search: `%${search}%` }
      );
    }

    // 分页和排序
    const offset = (page - 1) * limit;
    queryBuilder
      .orderBy('data.createdAt', 'DESC')
      .skip(offset)
      .take(limit);

    const [data, total] = await queryBuilder.getManyAndCount();

    return {
      success: true,
      data,
      pagination: {
        page,
        limit,
        total,
        totalPages: Math.ceil(total / limit),
      },
    };
  }

  // 获取单条数据
  async getDataById(id: string): Promise<CrawledData | null> {
    return await this.dataRepository.findOne({
      where: { id },
      relations: ['task'],
    });
  }

  // 删除数据
  async deleteData(id: string): Promise<boolean> {
    const result = await this.dataRepository.delete(id);
    return (result.affected ?? 0) > 0;
  }

  // 批量删除数据
  async bulkDeleteData(ids: string[]): Promise<number> {
    const result = await this.dataRepository.delete(ids);
    return result.affected || 0;
  }

  // 标记为已处理
  async markAsProcessed(id: string): Promise<CrawledData | null> {
    const data = await this.getDataById(id);
    if (!data) return null;

    data.markAsProcessed();
    return await this.dataRepository.save(data);
  }

  // 标记为无效
  async markAsInvalid(id: string, reason?: string): Promise<CrawledData | null> {
    const data = await this.getDataById(id);
    if (!data) return null;

    data.markAsInvalid(reason);
    return await this.dataRepository.save(data);
  }

  // 导出数据
  async exportData(query: any): Promise<string | Buffer> {
    const { format = 'json', taskId, startDate, endDate } = query;

    const queryBuilder = this.dataRepository
      .createQueryBuilder('data')
      .leftJoinAndSelect('data.task', 'task');

    if (taskId) {
      queryBuilder.andWhere('data.taskId = :taskId', { taskId });
    }

    if (startDate) {
      queryBuilder.andWhere('data.createdAt >= :startDate', { startDate });
    }

    if (endDate) {
      queryBuilder.andWhere('data.createdAt <= :endDate', { endDate });
    }

    const data = await queryBuilder.getMany();

    switch (format) {
      case 'csv':
        return this.exportToCsv(data);
      case 'xlsx':
        return this.exportToXlsx(data);
      default:
        return JSON.stringify(data, null, 2);
    }
  }

  // 获取数据统计
  async getDataStats() {
    const total = await this.dataRepository.count();
    const valid = await this.dataRepository.count({ where: { isValid: true } });
    const processed = await this.dataRepository.count({ where: { isProcessed: true } });

    // 按引擎统计
    const byEngine = await this.dataRepository
      .createQueryBuilder('data')
      .select('data.engine', 'engine')
      .addSelect('COUNT(*)', 'count')
      .groupBy('data.engine')
      .getRawMany();

    // 按任务统计
    const byTask = await this.dataRepository
      .createQueryBuilder('data')
      .leftJoin('data.task', 'task')
      .select('task.name', 'taskName')
      .addSelect('COUNT(*)', 'count')
      .groupBy('task.name')
      .orderBy('count', 'DESC')
      .limit(10)
      .getRawMany();

    // 按日期统计（最近7天）
    const byDate = await this.dataRepository
      .createQueryBuilder('data')
      .select('DATE(data.createdAt)', 'date')
      .addSelect('COUNT(*)', 'count')
      .where('data.createdAt >= :date', { date: new Date(Date.now() - 7 * 24 * 60 * 60 * 1000) })
      .groupBy('DATE(data.createdAt)')
      .orderBy('date', 'ASC')
      .getRawMany();

    return {
      total,
      valid,
      processed,
      invalid: total - valid,
      unprocessed: total - processed,
      byEngine: byEngine.reduce((acc, item) => {
        acc[item.engine] = parseInt(item.count);
        return acc;
      }, {}),
      byTask: byTask.map(item => ({
        taskName: item.taskName,
        count: parseInt(item.count),
      })),
      byDate: byDate.map(item => ({
        date: item.date,
        count: parseInt(item.count),
      })),
    };
  }

  // 获取任务数据统计
  async getTaskDataStats(taskId: string) {
    const total = await this.dataRepository.count({ where: { taskId } });
    const valid = await this.dataRepository.count({ where: { taskId, isValid: true } });
    const processed = await this.dataRepository.count({ where: { taskId, isProcessed: true } });

    // 最近的数据
    const recent = await this.dataRepository.find({
      where: { taskId },
      order: { createdAt: 'DESC' },
      take: 10,
    });

    return {
      total,
      valid,
      processed,
      invalid: total - valid,
      unprocessed: total - processed,
      recent,
    };
  }

  // 数据去重
  async deduplicateData(taskId?: string, dryRun = false) {
    const queryBuilder = this.dataRepository
      .createQueryBuilder('data')
      .select('data.hash', 'hash')
      .addSelect('COUNT(*)', 'count')
      .addSelect('MIN(data.id)', 'keepId')
      .addSelect('ARRAY_AGG(data.id)', 'allIds')
      .groupBy('data.hash')
      .having('COUNT(*) > 1');

    if (taskId) {
      queryBuilder.andWhere('data.taskId = :taskId', { taskId });
    }

    const duplicates = await queryBuilder.getRawMany();

    if (dryRun) {
      return {
        duplicateGroups: duplicates.length,
        totalDuplicates: duplicates.reduce((sum, dup) => sum + parseInt(dup.count) - 1, 0),
        duplicates: duplicates.map(dup => ({
          hash: dup.hash,
          count: parseInt(dup.count),
          keepId: dup.keepId,
          duplicateIds: dup.allIds.filter((id: number) => id !== dup.keepId),
        })),
      };
    }

    // 执行去重
    let deletedCount = 0;
    for (const duplicate of duplicates) {
      const idsToDelete = duplicate.allIds.filter((id: string) => id !== duplicate.keepId);
      const result = await this.dataRepository.delete(idsToDelete);
      deletedCount += result.affected || 0;
    }

    return {
      duplicateGroups: duplicates.length,
      deletedCount,
    };
  }

  // 导出为CSV
  private exportToCsv(data: CrawledData[]): string {
    if (data.length === 0) return '';

    const headers = ['ID', 'Task Name', 'URL', 'Engine', 'Valid', 'Processed', 'Created At', 'Data'];
    const rows = data.map(item => [
      item.id,
      item.task?.name || '',
      item.url,
      item.engine || '',
      item.isValid ? 'Yes' : 'No',
      item.isProcessed ? 'Yes' : 'No',
      item.createdAt.toISOString(),
      JSON.stringify(item.data),
    ]);

    return [headers, ...rows]
      .map(row => row.map(cell => `"${String(cell).replace(/"/g, '""')}"`).join(','))
      .join('\n');
  }

  // 导出为XLSX
  private exportToXlsx(data: CrawledData[]): Buffer {
    // 这里需要使用 xlsx 库来生成 Excel 文件
    // 暂时返回 JSON 格式
    return Buffer.from(JSON.stringify(data, null, 2));
  }
}
